import datetime
import json
import pymysql
from dateutil.relativedelta import relativedelta


# noinspection PyBroadException
class MyDatabase:
    def __init__(self, environment):
        if environment:
            self.test = {'host': '172.31.110.157', 'port': 3306, 'user': 'admin', 'password': 'Mysql@1234',
                         'database': 'tangram_v3_dev'}
        else:
            self.test = {'host': '172.31.110.143', 'port': 3306, 'user': 'admin', 'password': 'Mysql@123456',
                         'database': 'tangram_v3_uat'}

    def modify_phone(self, phone):
        sql_one = f"select identification from org_user where mobile_no ='{phone}'"
        sql_two = f"UPDATE org_user SET identification = 'Y' WHERE mobile_no = '{phone}'"
        try:
            db = pymysql.connect(
                host=self.test['host'], port=self.test['port'], user=self.test['user'], password=self.test['password'],
                database=self.test['database'], connect_timeout=5)
            cursor = db.cursor()
        except:
            return 1
        else:
            try:
                res = cursor.execute(sql_one)
                if res > 0:
                    results = cursor.fetchall()
                    for row in results:
                        if row[0] == 'Y':
                            pass
                        else:
                            try:
                                cursor.execute(sql_two)
                                db.commit()
                            except:
                                db.close()
                                return 2
                            else:
                                cursor.execute(sql_one)
                                results = cursor.fetchall()
                                for new_row in results:
                                    if new_row[0] == 'Y':
                                        pass
                                    else:
                                        db.close()
                                        return 4
                else:
                    db.close()
                    return 0
            except:
                db.close()
                return 2

    def del_promotion_factor(self, spu_id):
        sql_one = f"select * from rate_factor_base where spu_id = '{spu_id}'"
        sql_two = f"select * from rate_factor_rule where spu_id = '{spu_id}'"
        sql_three = f"select * from rate_factor where spu_id = '{spu_id}'"
        del_sql_one = f"delete from rate_factor_base where spu_id = '{spu_id}'"
        del_sql_two = f"delete from rate_factor_rule where spu_id = '{spu_id}'"
        del_sql_three = f"delete from rate_factor where spu_id = '{spu_id}'"
        del_sql_four = f"delete from rate_factor_version where spu_id = '{spu_id}'"
        try:
            db = pymysql.connect(
                host=self.test['host'], port=self.test['port'], user=self.test['user'], password=self.test['password'],
                database=self.test['database'], connect_timeout=5)
            cursor = db.cursor()
        except:
            return 1
        else:
            try:
                res = cursor.execute(sql_one)
                res1 = cursor.execute(sql_two)
                res2 = cursor.execute(sql_three)
                if res > 0 or res1 > 0 or res2 > 0:
                    cursor.execute(del_sql_one)
                    cursor.execute(del_sql_two)
                    cursor.execute(del_sql_three)
                    cursor.execute(del_sql_four)
                    db.commit()
                    res = cursor.execute(sql_one)
                    res1 = cursor.execute(sql_two)
                    res2 = cursor.execute(sql_three)
                    if res > 0 or res1 > 0 or res2 > 0:
                        db.close()
                        return 4
                    else:
                        db.close()
                        return 6
                else:
                    db.close()
                    return 0
            except:
                db.close()
                return 2

    def modify_hesitation_period(self, policy_id):
        po_start = f"select started_time from policy where policy_id='{policy_id}'"
        po_end = f"select ended_time from policy where policy_id='{policy_id}'"
        p_po_start = f"select started_time from p_policy where policy_id='{policy_id}'"
        # 犹豫期结束
        extend_end = f"select key_value from policy_extends where policy_id = '{policy_id}' and key_code = 'EARLIESTSETTLEMENTTIME'"
        # 犹豫期开始
        extend_start = f"select key_value from policy_extends where policy_id = '{policy_id}' and key_code = 'HESITATEEBEGINDATE'"
        try:
            db = pymysql.connect(
                host=self.test['host'], port=self.test['port'], user=self.test['user'], password=self.test['password'],
                database=self.test['database'], connect_timeout=5)
            cursor = db.cursor()
        except:
            return 1
        else:
            try:
                res = cursor.execute(po_start)
                res_po_start = cursor.fetchall()
                res1 = cursor.execute(p_po_start)
                cursor.fetchall()
                res2 = cursor.execute(extend_end)
                res_extend_end = cursor.fetchall()
                if res == 0 or res1 == 0:
                    db.close()
                    return 0
                elif res2 == 0:
                    db.close()
                    return 10
                else:
                    cursor.execute(extend_start)
                    res_extend_start = cursor.fetchall()
                    if res_extend_start[0] == res_extend_end[0]:
                        db.close()
                        return 10
                    else:
                        extend_start_time = datetime.datetime.strptime(res_extend_start[0][0], "%Y-%m-%d %H:%M:%S")
                        extend_end_time = datetime.datetime.strptime(res_extend_end[0][0], "%Y-%m-%d %H:%M:%S")
                        now_time = datetime.datetime.now()
                        po_start_time = res_po_start[0][0]
                        new_data = (now_time + datetime.timedelta(days=-1)).strftime("%Y-%m-%d %H:%M:%S")
                        if extend_start_time <= now_time <= extend_end_time:
                            if extend_start_time <= now_time <= po_start_time:
                                up_po_start = f"update policy set started_time = '{new_data}' where policy_id='{policy_id}'"
                                up_p_po_start = f"update p_policy set started_time = '{new_data}' where policy_id='{policy_id}'"
                                cursor.execute(up_po_start)
                                cursor.execute(up_p_po_start)
                                db.commit()
                                db.close()
                                return 6
                            else:
                                db.close()
                                return 11
                        elif now_time <= extend_start_time:
                            cursor.execute(po_end)
                            res_po_end = cursor.fetchall()
                            po_end_time = res_po_end[0][0]
                            a = datetime.date(extend_start_time.year, extend_start_time.month, extend_start_time.day)
                            b = datetime.date(now_time.year, now_time.month, now_time.day)
                            res_data = (a - b).days
                            new_start_time = (po_start_time + datetime.timedelta(days=-res_data)).strftime("%Y-%m-%d %H:%M:%S")
                            new_end_time = (po_end_time + datetime.timedelta(days=-res_data)).strftime("%Y-%m-%d %H:%M:%S")
                            new_extend_start_time = (extend_start_time + datetime.timedelta(days=-res_data)).strftime("%Y-%m-%d %H:%M:%S")
                            new_extend_end_time = (extend_end_time + datetime.timedelta(days=-res_data)).strftime("%Y-%m-%d %H:%M:%S")
                            up_po_start = f"update policy set started_time = '{new_start_time}' where policy_id='{policy_id}'"
                            up_p_po_start = f"update policy set ended_time = '{new_end_time}' where policy_id='{policy_id}'"
                            up_po_end = f"update p_policy set started_time = '{new_start_time}' where policy_id='{policy_id}'"
                            up_p_po_end = f"update p_policy set ended_time = '{new_end_time}' where policy_id='{policy_id}'"
                            up_extend_start = f"update policy_extends set key_value = '{new_extend_start_time}' where policy_id='{policy_id}' and key_code = 'HESITATEEBEGINDATE'"
                            up_extend_end = f"update policy_extends set key_value = '{new_extend_end_time}' where policy_id='{policy_id}' and key_code = 'EARLIESTSETTLEMENTTIME'"
                            cursor.execute(up_po_start)
                            cursor.execute(up_p_po_start)
                            cursor.execute(up_po_end)
                            cursor.execute(up_p_po_end)
                            cursor.execute(up_extend_start)
                            cursor.execute(up_extend_end)
                            db.commit()
                            db.close()
                            return 6
                        else:
                            db.close()
                            return 12
            except:
                db.close()
                return 2

    def modify_surrender(self, policy_id):
        sel_po_start = f"select started_time from policy where policy_id='{policy_id}'"
        sel_po_end = f"select ended_time from policy where policy_id='{policy_id}'"
        sel_p_po_start = f"select started_time from p_policy where policy_id='{policy_id}'"
        # 犹豫期结束
        sel_extend_end = f"select key_value from policy_extends where policy_id = '{policy_id}' and key_code = 'EARLIESTSETTLEMENTTIME'"
        # 犹豫期开始
        sel_extend_start = f"select key_value from policy_extends where policy_id = '{policy_id}' and key_code = 'HESITATEEBEGINDATE'"
        try:
            db = pymysql.connect(
                host=self.test['host'], port=self.test['port'], user=self.test['user'], password=self.test['password'],
                database=self.test['database'], connect_timeout=5)
            cursor = db.cursor()
        except:
            return 1
        else:
            try:
                res = cursor.execute(sel_po_start)
                res_po_start = cursor.fetchall()
                res1 = cursor.execute(sel_p_po_start)
                res2 = cursor.execute(sel_extend_end)
                res_extend_end = cursor.fetchall()
                cursor.execute(sel_extend_start)
                res_extend_start = cursor.fetchall()
                extend_start_time = datetime.datetime.strptime(res_extend_start[0][0], "%Y-%m-%d %H:%M:%S")
                extend_end_time = datetime.datetime.strptime(res_extend_end[0][0], "%Y-%m-%d %H:%M:%S")
                now_time = datetime.datetime.now()
                po_start_time = res_po_start[0][0]
                cursor.execute(sel_po_end)
                res_po_end = cursor.fetchall()
                po_end_time = res_po_end[0][0]
                if res == 0 or res1 == 0:
                    db.close()
                    return 0
                elif res2 == 0:
                    if now_time > po_start_time:
                        db.close()
                        return 6
                    else:
                        new_start_time = (po_start_time + datetime.timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
                        new_end_time = (po_end_time + datetime.timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
                        up_po_start = f"update policy set started_time = '{new_start_time}' where policy_id='{policy_id}'"
                        up_p_po_start = f"update policy set ended_time = '{new_end_time}' where policy_id='{policy_id}'"
                        up_po_end = f"update p_policy set started_time = '{new_start_time}' where policy_id='{policy_id}'"
                        up_p_po_end = f"update p_policy set ended_time = '{new_end_time}' where policy_id='{policy_id}'"
                        cursor.execute(up_po_start)
                        cursor.execute(up_p_po_start)
                        cursor.execute(up_po_end)
                        cursor.execute(up_p_po_end)
                        db.commit()
                        db.close()
                        return 6
                else:
                    if now_time > extend_end_time and now_time > po_start_time:
                        db.close()
                        return 6
                    else:
                        new_start_time = (po_start_time + datetime.timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
                        new_end_time = (po_end_time + datetime.timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
                        new_extend_start_time = (extend_start_time + datetime.timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
                        new_extend_end_time = (extend_end_time + datetime.timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
                        up_po_start = f"update policy set started_time = '{new_start_time}' where policy_id='{policy_id}'"
                        up_p_po_start = f"update policy set ended_time = '{new_end_time}' where policy_id='{policy_id}'"
                        up_po_end = f"update p_policy set started_time = '{new_start_time}' where policy_id='{policy_id}'"
                        up_p_po_end = f"update p_policy set ended_time = '{new_end_time}' where policy_id='{policy_id}'"
                        up_extend_start = f"update policy_extends set key_value = '{new_extend_start_time}' where policy_id='{policy_id}' and key_code = 'HESITATEEBEGINDATE'"
                        up_extend_end = f"update policy_extends set key_value = '{new_extend_end_time}' where policy_id='{policy_id}' and key_code = 'EARLIESTSETTLEMENTTIME'"
                        cursor.execute(up_po_start)
                        cursor.execute(up_p_po_start)
                        cursor.execute(up_po_end)
                        cursor.execute(up_p_po_end)
                        cursor.execute(up_extend_start)
                        cursor.execute(up_extend_end)
                        db.commit()
                        db.close()
                        return 6
            except:
                db.close()
                return 2

    def modify_renewal(self, policy_id, choice_id):
        sel_renewal_on = f"SELECT start_renewal_time,due_payment_time,end_grace_time FROM policy_renewal_plan WHERE policy_id = '{policy_id}' and policy_year = '2'"
        sel_renewal_th = f"select policy_stand_conent from p_policy_detail where policy_uuid = (select policy_uuid from p_policy where policy_id = '{policy_id}')"
        sel_renewal_fo = f"select policy_stand_conent,started_time,ended_time from policy where policy_id = '{policy_id}'"
        try:
            db = pymysql.connect(
                host=self.test['host'], port=self.test['port'], user=self.test['user'], password=self.test['password'],
                database=self.test['database'], connect_timeout=5)
            cursor = db.cursor()
        except:
            return 1
        else:
            try:
                res1 = cursor.execute(sel_renewal_on)
                on_res = cursor.fetchall()
                cursor.execute(sel_renewal_th)
                th_res = cursor.fetchall()
                cursor.execute(sel_renewal_fo)
                fo_res = cursor.fetchall()
                if res1 == 0:
                    db.close()
                    return 0
                else:
                    if on_res[0][0] <= datetime.datetime.now() <= on_res[0][2]:
                        db.close()
                        return 6
                    elif datetime.datetime.now() >= on_res[0][2]:
                        db.close()
                        return 13
                    else:
                        be_day = (on_res[0][0] - datetime.datetime.now()).days
                        en_day = (on_res[0][2] - datetime.datetime.now()).days
                        if choice_id == '前一天':
                            start_res = (on_res[0][0] - relativedelta(days=be_day))
                            due_res = (on_res[0][1] - relativedelta(days=be_day))
                            end_res = (on_res[0][2] - relativedelta(days=be_day))
                            new_begin = (due_res - relativedelta(days=be_day)).strftime("%Y-%m-%d %H:%M:%S")
                            new_start_time = (fo_res[0][1] - relativedelta(days=be_day))
                            new_end_time = (fo_res[0][2] - relativedelta(days=be_day))
                        elif choice_id == '第一天':
                            start_res = (on_res[0][0] - relativedelta(days=(be_day + 1)))
                            due_res = (on_res[0][1] - relativedelta(days=(be_day + 1)))
                            end_res = (on_res[0][2] - relativedelta(days=(be_day + 1)))
                            new_begin = (due_res - relativedelta(days=(be_day + 1))).strftime("%Y-%m-%d %H:%M:%S")
                            new_start_time = (fo_res[0][1] - relativedelta(days=(be_day + 1)))
                            new_end_time = (fo_res[0][2] - relativedelta(days=(be_day + 1)))
                        elif choice_id == '最后一天':
                            start_res = (on_res[0][0] - relativedelta(days=en_day))
                            due_res = (on_res[0][1] - relativedelta(days=en_day))
                            end_res = (on_res[0][2] - relativedelta(days=en_day))
                            new_begin = (due_res - relativedelta(days=en_day)).strftime("%Y-%m-%d %H:%M:%S")
                            new_start_time = (fo_res[0][1] - relativedelta(days=en_day))
                            new_end_time = (fo_res[0][2] - relativedelta(days=en_day))
                        else:
                            start_res = (on_res[0][0] - relativedelta(days=(en_day + 1)))
                            due_res = (on_res[0][1] - relativedelta(days=(en_day + 1)))
                            end_res = (on_res[0][2] - relativedelta(days=(en_day + 1)))
                            new_begin = (due_res - relativedelta(days=(en_day + 1))).strftime("%Y-%m-%d %H:%M:%S")
                            new_start_time = (fo_res[0][1] - relativedelta(days=(en_day + 1)))
                            new_end_time = (fo_res[0][2] - relativedelta(days=(en_day + 1)))
                        update_on = f"update policy_renewal_plan set start_renewal_time = '{start_res}',due_payment_time = '{due_res}',end_grace_time = '{end_res}' WHERE policy_id = '{policy_id}' and policy_year = '2'"
                        update_tw = f"update policy_renewal_record set start_renewal_time = '{start_res}',due_payment_time = '{due_res}',end_grace_time = '{end_res}',renewal_status = '2' WHERE policy_id = '{policy_id}' and policy_year = '2'"
                        j = json.loads(th_res[0][0])
                        j['calculator']['beginDate'] = new_begin
                        new_json = json.dumps(j, ensure_ascii=False)
                        update_th = f"update p_policy_detail set policy_stand_conent = '{new_json}' where policy_uuid = (select policy_uuid from p_policy where policy_id = '{policy_id}')"
                        update_fo = f"update policy set policy_stand_conent = '{new_json}',started_time = '{new_start_time}',ended_time = '{new_end_time}' where policy_id = '{policy_id}'"
                        update_fi = f"update p_policy set started_time = '{new_start_time}',ended_time = '{new_end_time}' where policy_id = '{policy_id}'"
                        update_si = f"update proposal set proposal_standard_content = '{new_json}' where proposal_id = (select proposal_id from policy where policy_id = '{policy_id}')"
                        cursor.execute(update_on)
                        cursor.execute(update_tw)
                        cursor.execute(update_th)
                        cursor.execute(update_fo)
                        cursor.execute(update_fi)
                        cursor.execute(update_si)
                        db.commit()
                        db.close()
                        return 6
            except:
                db.close()
                return 2

    def modify_renew(self, policy_id, choice_id):
        sel_renewal_on = f"SELECT start_renew_time,end_grace_time FROM policy_renew_record WHERE policy_id = '{policy_id}'"
        sel_renewal_tw = f"select policy_stand_conent from p_policy_detail where policy_uuid = (select policy_uuid from p_policy where policy_id = '{policy_id}')"
        try:
            db = pymysql.connect(
                host=self.test['host'], port=self.test['port'], user=self.test['user'], password=self.test['password'],
                database=self.test['database'], connect_timeout=5)
            cursor = db.cursor()
        except:
            return 1
        else:
            try:
                res1 = cursor.execute(sel_renewal_on)
                on_res = cursor.fetchall()
                cursor.execute(sel_renewal_tw)
                tw_res = cursor.fetchall()
                if res1 == 0:
                    db.close()
                    return 0
                else:
                    now = datetime.datetime.now()
                    start_renew_time = on_res[0][0]
                    end_grace_time = on_res[0][1]
                    if start_renew_time < now < end_grace_time:
                        db.close()
                        return 6
                    elif now > end_grace_time:
                        db.close()
                        return 14
                    else:
                        j = json.loads(tw_res[0][0])
                        begin_date = j['calculator']['beginDate']
                        end_date = j['calculator']['endDate']
                        begin = datetime.datetime.strptime(begin_date, "%Y-%m-%d %H:%M:%S")
                        end = datetime.datetime.strptime(end_date, "%Y-%m-%d %H:%M:%S")
                        be_day = (start_renew_time - datetime.datetime.now()).days
                        en_day = (end_grace_time - datetime.datetime.now()).days
                        if choice_id == '前一天':
                            new_start_renew_time = (on_res[0][0] - relativedelta(days=be_day))
                            new_end_grace_time = (end_grace_time - relativedelta(days=be_day))
                            new_begin = (begin - relativedelta(days=be_day))
                            new_end = (end - relativedelta(days=be_day))
                        elif choice_id == '第一天':
                            new_start_renew_time = (start_renew_time - relativedelta(days=(be_day + 1)))
                            new_end_grace_time = (end_grace_time - relativedelta(days=(be_day + 1)))
                            new_begin = (begin - relativedelta(days=(be_day + 1)))
                            new_end = (end - relativedelta(days=(be_day + 1)))
                        elif choice_id == '最后一天':
                            new_start_renew_time = (start_renew_time - relativedelta(days=en_day))
                            new_end_grace_time = (end_grace_time - relativedelta(days=en_day))
                            new_begin = (begin - relativedelta(days=en_day))
                            new_end = (end - relativedelta(days=en_day))
                        else:
                            new_start_renew_time = (start_renew_time - relativedelta(days=(en_day + 1)))
                            new_end_grace_time = (end_grace_time - relativedelta(days=(en_day + 1)))
                            new_begin = (begin - relativedelta(days=(en_day + 1)))
                            new_end = (end - relativedelta(days=(en_day + 1)))
                        j['calculator']['beginDate'] = new_begin.strftime("%Y-%m-%d %H:%M:%S")
                        j['calculator']['endDate'] = new_end.strftime("%Y-%m-%d %H:%M:%S")
                        new_json = json.dumps(j, ensure_ascii=False)
                        update_on = f"update policy_renew_record set start_renew_time = '{new_start_renew_time}',end_grace_time = '{new_end_grace_time}' WHERE policy_id = '{policy_id}'"
                        update_tw = f"update p_policy_detail set policy_stand_conent = '{new_json}' WHERE policy_uuid = (select policy_uuid from p_policy where policy_id = '{policy_id}')"
                        update_th = f"update policy set policy_stand_conent = '{new_json}',started_time = '{new_begin}',ended_time = '{new_end}' where policy_id = '{policy_id}'"
                        update_fo = f"update p_policy set started_time = '{new_begin}',ended_time = '{new_end}' where policy_id = '{policy_id}'"
                        update_fi = f"update proposal set proposal_standard_content = '{new_json}' where proposal_id = (select proposal_id from policy where policy_id = '{policy_id}')"
                        update_si = f"update ins_p_policy_track set started_time = '{new_begin}',ended_time = '{new_end}' where policy_id='{policy_id}'"
                        update_se = f"update p_policy_detail_track set policy_stand_conent = '{new_json}' where policy_uuid = (select policy_uuid from p_policy where policy_id = '{policy_id}')"
                        cursor.execute(update_on)
                        cursor.execute(update_tw)
                        cursor.execute(update_th)
                        cursor.execute(update_fo)
                        cursor.execute(update_fi)
                        cursor.execute(update_si)
                        cursor.execute(update_se)
                        db.commit()
                        db.close()
                        return 6
            except:
                db.close()
                return 2
